package com.jsmtr.rms.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import com.jsmtr.rms.entity.*;
import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.service.IDoBSService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.EnumTools;
import com.jsmtr.rms.utils.db.DbUtil;

@Service
public class DoBSService implements IDoBSService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @Override
    public Map<String, Object> getBSRooms(int page, int perpage, DoBSRoom room) throws Exception {
        StringBuffer queryHQL = new StringBuffer("from DoBSRoom r where 1=1 order by priority asc");
        StringBuffer countHQL = new StringBuffer("select count(*) from DoBSRoom r where 1=1 ");
        StringBuffer condition = new StringBuffer();
        condition.append(BeanTools.hqlWhere(room, "r"));

        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public DoBSRoom getRoomByName(String name) throws Exception {
        return (DoBSRoom) dao.findUnique("from DoBSRoom where name='" + name + "'");
    }

    @Override
    public DoBSRoom getRoomByAddress(String address) throws Exception {
        return (DoBSRoom) dao.findUnique("from DoBSRoom where address='" + address + "'");
    }

    @Override
    public DoBSRoom getRoomById(String id) throws Exception {
        return (DoBSRoom) dao.findUnique("from DoBSRoom where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoBSRoom> getRooms() throws Exception {
        return dao.find("from DoBSRoom where state='启用' order by name");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> getRoomsAddress() throws Exception {
        return dao.executeSQLQuery("select distinct(address) from DOBS_ROOM where state='启用'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoBSRoom> getRooms(String[] ids) throws Exception {
        return dao.find("from DoBSRoom where id in(:idlist)", ids);
    }

    @Override
    public Map<String, Object> getBSExams(int page, int perpage, DoBSExam exam) throws Exception {
        StringBuffer queryHQL = new StringBuffer("select m.id,m.roomid,m.name,m.starttime,m.endtime,r.name,r.allcount ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");
        StringBuffer condition = new StringBuffer();
        condition.append("from DoBSRoom r,DoBSExam m where r.id=m.roomid ");
        condition.append(BeanTools.hqlWhere(exam, "m"));

        queryHQL.append(condition);
        countHQL.append(condition);

        return DbUtil.pageQuery(page, perpage, queryHQL.toString(), countHQL.toString(), dao);
    }

    @Override
    public DoBSExam getExamById(String id) throws Exception {
        return (DoBSExam) dao.findUnique("from DoBSExam where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoBSExam> getExams(String[] ids) throws Exception {
        return dao.find("from DoBSExam where id in(:idlist)", ids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoBSExam> getExams(String rid) throws Exception {
        return dao.find("from DoBSExam where roomid='" + rid + "'");
    }

    @Override
    public DoBSUser getBsUser(String userid, String fid) throws Exception {
        return (DoBSUser) dao.findUniqueNew("from DoBSUser where userid='" + userid + "' and fid='" + fid + "'");
    }

    @Override
    public DoBSUser getBsUserByRandom(String fid) throws Exception {
        return (DoBSUser) dao.findUnique("from DoBSUser where fid='" + fid + "'");
    }

    @Override
    public List<Object[]> getBsUsers(String fid, String[] jnames, int recruittype) throws Exception {
        /*if (recruittype == EnumTools.RecruitType.内部招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id,r.name,r.sex,r.code,r.tel,r.jname,u.kcname,u.ticketnum,u.seatnum,u.zhszpoint,u.zhszresult,u.gxcppoint,u.gxcpresult,u.zycppoint,u.zycpresult,u.point,u.result ");
            sql.append("from DNZResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) and r.id in(:idlist)");
            return dao.findSql(sql.toString(), jnames);
        }*/
        if (recruittype == EnumTools.RecruitType.社会招聘.ordinal()||recruittype==EnumTools.RecruitType.高校校园招聘.ordinal()
                ||recruittype==EnumTools.RecruitType.中专校园招聘.ordinal()||recruittype==EnumTools.RecruitType.内部招聘.ordinal()
                ||recruittype==EnumTools.RecruitType.驾驶员招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select r.name,r.cardno,u.ticketnum,u.kcname,r.jname,u.seatnum ");
            sql.append("from DUserResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid and r.jname in(:idlist) order by u.ticketnum asc");
            return dao.findSql(sql.toString(), jnames);
        }
        return null;
    }

    @Override
    public DoBSUser getBsUser(String id) throws Exception {
        return (DoBSUser) dao.findUnique("from DoBSUser where id='" + id + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoBSUser> getBsUsers(String[] uids, String fid) throws Exception {
        return dao.find("from DoBSUser where fid='" + fid + "' and userid in(:idlist)", uids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DoSSUser> getSsUsers(String[] uids, String fid) throws Exception {
        return dao.find("from DoSSUser where fid='" + fid + "' and userid in(:idlist)", uids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getBsUser(String[] uids, String fid, int recruittype) throws Exception {
        /*if (recruittype == EnumTools.RecruitType.内部招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id,r.name,r.tel,u.starttime,u.address,u.notice ");
            sql.append("from DNZResume r,DoBS_User u where r.id=u.userid and u.fid='" + fid + "' and r.id in(:idlist)");
            System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
        }*/
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id,r.name,r.tel,u.starttime,u.address,u.notice ");
            sql.append("from DUserResume r,DoBS_User u where r.id=u.userid and u.fid='" + fid + "' and r.id in(:idlist)");
            System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getBsPlan(String fid, String[] uids, int recruittype) throws Exception {
       /* if (recruittype == EnumTools.RecruitType.内部招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select u.id,r.name,r.sex,r.code,r.tel,r.jname,u.kcname,u.ticketnum,u.seatnum,u.zhszpoint,u.zhszresult,u.gxcppoint,u.gxcpresult,u.zycppoint,u.zycpresult,u.point,u.result ");
            sql.append("from DNZResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) and r.id in(:idlist)");
            //System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
        }*/
        if (recruittype == EnumTools.RecruitType.社会招聘.ordinal()||recruittype==EnumTools.RecruitType.高校校园招聘.ordinal()
                ||recruittype==EnumTools.RecruitType.中专校园招聘.ordinal()||recruittype==EnumTools.RecruitType.内部招聘.ordinal()
                ||recruittype==EnumTools.RecruitType.驾驶员招聘.ordinal()) {
                StringBuilder sql = new StringBuilder();
            sql.append("select u.id,r.name,r.sexname,r.cardno,r.tel,r.jname,u.kcname,u.ticketnum,u.seatnum,u.zhszpoint,u.zhszresult,u.gxcppoint,u.gxcpresult,u.zycppoint,u.zycpresult,u.point,u.result ");
            sql.append("from DUserResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) and r.id in(:idlist)");
           // System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getBsTicket(String fid, String[] uids, int recruittype) throws Exception {
       /* if (recruittype == EnumTools.RecruitType.内部招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select r.name,r.code,r.sex,r.tel,r.jname,u.kcname,u.ticketnum,u.seatnum ");
            sql.append("from DNZResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) and r.id in(:idlist)");
            System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
        }*/
        if (recruittype == EnumTools.RecruitType.社会招聘.ordinal()||recruittype==EnumTools.RecruitType.高校校园招聘.ordinal()
                ||recruittype==EnumTools.RecruitType.内部招聘.ordinal()||recruittype==EnumTools.RecruitType.驾驶员招聘.ordinal()) {
            StringBuilder sql = new StringBuilder();
            sql.append("select r.name,r.cardno,r.sexname,r.tel,r.jname,u.kcname,u.ticketnum,u.seatnum ");
            sql.append("from DUserResume r,(select * from DoBS_User where fid='" + fid + "') u where r.id=u.userid(+) and r.id in(:idlist)");
            System.out.println(sql.toString());
            return dao.findSql(sql.toString(), uids);
        }
        return null;
    }

    @Override
    public int getUserCount(String fid, String kcname, String starttime) throws Exception {
        String sql = "select count(distinct(ticketnum)) from DoBS_User where fid='" + fid + "' and kcname='" + kcname + "' and starttime='" + starttime + "'";
        return Integer.parseInt(dao.findUniqueSql(sql).toString());
    }

    @Override
    public int getUserCountNew(String fid, String kcname, String starttime) throws Exception {
        String sql = "select count(distinct(ticketnum)) from DoBS_User where fid='" + fid + "' and kcname='" + kcname + "' and starttime='" + starttime + "' and startnum = '-1";
        return Integer.parseInt(dao.findUniqueSqlNew(sql).toString());
    }

    @Override
    public int getTicketCount(String year) throws Exception {
        String sql = "select count(distinct(ticketnum)) from DoBS_User where TICKETNUM like '" + year + "%'";
        return Integer.parseInt(dao.findUniqueSql(sql).toString());
    }

    @Override
    public int getTicketCountNew(String year) throws Exception {
        String sql = "select count(ticketnum) from DoBS_User where TICKETNUM like '" + year + "%'";
        return Integer.parseInt(dao.findUniqueSql(sql).toString());
    }

    @Override
    public List getBsUserByCardNo(String cardNo, String fid) throws Exception {
        Map<String,Object> params=new HashMap();
        params.put("cardno",cardNo);
        params.put("fid",fid);
        return dao.find("select bu from DoBSUser as bu,DUserResume as u where bu.userid=u.id and u.cardno=:cardno and bu.fid=:fid", params);

    }

    @Override
    public List getBsUserByTicketnumAndJname(String ticketnum,String jname,String fid) throws Exception {
        Map<String,Object> params=new HashMap();
        params.put("ticketnum",ticketnum);
        params.put("jname",jname);
        params.put("fid",fid);
        return dao.find("select bu from DoBSUser as bu,DUserResume as u where bu.userid=u.id and bu.ticketnum=:ticketnum and u.jname=:jname and bu.fid=:fid", params);

    }

    @Override
    public List getCanUseRoom(String starttime, String endtime) throws Exception {
        Map<String,Object> params=new HashMap();
        params.put("starttime",starttime);
        params.put("endtime",endtime);
        String sql= "select * from(\n" +
                        "select br.id,br.name,(br.allcount-nvl(temp.ybrs,0)) kbrs from dobs_room br , (\n" +
                        "select kcsyqk.id,kcsyqk.name,kcsyqk.kcname,kcsyqk.starttime,kcsyqk.endtime,ybrs  from\n" +
                        "(select r.id,r.name,u.kcname,e.starttime,e.endtime,count(1) ybrs  from dobs_user u ,dobs_room r ,dobs_exam e\n" +
                        "where (u.address=r.name or u.address=r.address)\n" +
                        "and r.id=e.roomid\n" +
                        "and u.kcname=e.name\n" +
                        "group by r.id,r.name,u.kcname,e.starttime,e.endtime,r.allcount\n" +
                        ") kcsyqk  where ( kcsyqk.starttime between :starttime and :endtime ) or ( kcsyqk.endtime between :starttime and :endtime )\n" +
                        ") temp where br.id=temp.id(+)\n" +
                        ") where kbrs>0";
        return dao.executeSQLQuery(sql,params);
    }


    @Override
    public List<String> FastPlan(String fid, String pid, String chose) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select * ");
        sql.append(" from (select r.id from DUserResume r,DoBS_User u where r.fid='"+fid+"' and r.state in ('通过','已通过') and r.id=u.userid(+) and JNAME='" + chose + "' and PROID='" + pid + "' and fqstate='未放弃' and u.ticketnum is null) where 1=1");
        return dao.findSql(sql.toString());
    }

    @Override
    public List getCanUseRoomByAddress(String starttime, String endtime,String address) throws Exception {
        Map<String,Object> params=new HashMap();
        params.put("starttime",starttime);
        params.put("endtime",endtime);
        params.put("address",address);
        String sql= "select * from(\n" +
                "select br.id,br.address,(br.allcount-nvl(temp.ybrs,0)) kbrs,br.allcount counts,br.priority prio from dobs_room br , (\n" +
                "select kcsyqk.id,kcsyqk.name,kcsyqk.kcname,kcsyqk.starttime,kcsyqk.endtime,ybrs  from\n" +
                "(select r.id,r.name,u.kcname,e.starttime,e.endtime,count(1) ybrs  from dobs_user u ,dobs_room r ,dobs_exam e\n" +
                "where u.address=r.address\n" +
                "and r.id=e.roomid\n" +
                "and u.kcname=e.name\n" +
                "group by r.id,r.name,u.kcname,e.starttime,e.endtime,r.allcount\n" +
                ") kcsyqk  where ( kcsyqk.starttime between :starttime and :endtime ) or ( kcsyqk.endtime between :starttime and :endtime )\n" +
                ") temp where br.id=temp.id(+) and br.name=:address\n" +
                ") where kbrs=counts order by prio asc";
        return dao.executeSQLQuery(sql,params);
    }

    @Override
    public List getCanUseRoomByRoomId(String starttime, String endtime,String address) throws Exception {
        Map<String,Object> params=new HashMap();
        params.put("starttime",starttime);
        params.put("endtime",endtime);
        //params.put("address",address);
        //params.put("idlist",address);
        String sql= "select * from(\n" +
                "select br.id,br.address,(br.allcount-nvl(temp.ybrs,0)) kbrs,br.allcount counts,br.priority prio from dobs_room br , (\n" +
                "select kcsyqk.id,kcsyqk.name,kcsyqk.kcname,kcsyqk.starttime,kcsyqk.endtime,ybrs  from\n" +
                "(select r.id,r.name,u.kcname,e.starttime,e.endtime,count(1) ybrs  from dobs_user u ,dobs_room r ,dobs_exam e\n" +
                "where u.address=r.address\n" +
                "and r.id=e.roomid\n" +
                "and u.kcname=e.name\n" +
                "group by r.id,r.name,u.kcname,e.starttime,e.endtime,r.allcount\n" +
                ") kcsyqk  where ( kcsyqk.starttime between :starttime and :endtime ) or ( kcsyqk.endtime between :starttime and :endtime )\n" +
                ") temp where br.id=temp.id(+) and  br.id in ("+address+") \n" +
                ") where kbrs=counts order by prio asc";
        return dao.executeSQLQuery(sql,params);
    }

    @Override
    public Object getBSExamByName(String name) throws Exception {
        String hql=" from DoBSExam where name=:name";
        return dao.findUnique(hql,"name",name);
    }
}
